﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WaveMaster.Holidays.Club.SQL
{
    public class UserSQL
    {
        public static string GetAllUser()
        {
            string sql = "select * from user_master";
            return sql;
        }
        public static string GetAllUserByTravelAgent()
        {
            string sql = "select * from user_master where m_travelAgentId=?m_travelAgentId";
            return sql;
        }
        public static string GetMaxUserID()
        {
            return @"SELECT MAX(user_id) FROM user_master";
        }
        public static string GetUserByCode()
        {
            string sql = "select  * from user_master WHERE user_code = ?usercode";
            return sql;
        }
        public static string GetUserByCodeAndAgent()
        {
            string sql = "select  * from user_master WHERE user_code = ?usercode and m_travelAgentId=?m_travelAgentId";
            return sql;
        }
        public static string GetUserByID()
        {
            string sql = "select  * from user_master WHERE user_id = ?userid";
            return sql;
        }
        public static string InsertUser()
        {
            string sql = @"INSERT INTO user_master
           (user_code
           ,user_name
           ,password
           ,expiry_date
           ,active_code
           ,email_id
           ,cretn_date
           ,cretn_by
           ,modfctn_date
           ,modfctn_by
           ,modfctn_termnl            
            ,password_expired
            ,password_expiry_days
            ,m_travelAgentId
            ,m_userType
,m_macAddress,m_printerNameBP,m_printerNameWC,m_directPrintBP,m_directPrintWC
,m_directPrintKIMS
            )
     VALUES
           (?usercode
           ,?username
           ,?password
           ,?expirydate
           ,?activecode
           ,?emailid
           ,?createndate
           ,?createnby
           ,?modificationdate
           ,?modificationby
           ,?modificationterminal
            , ?passwordexpired, ?passwordexpirydays,?m_travelAgentId,?m_userType
,?m_macAddress,?m_printerNameBP,?m_printerNameWC,?m_directPrintBP,?m_directPrintWC
,?m_directPrintKIMS
    )";
            return sql;
        }
        public static string UpdateUser()
        {
            string sql = @"UPDATE user_master
   SET user_code = ?usercode
      ,user_name = ?username
      ,password = ?password
      ,expiry_date = ?expirydate
      ,active_code = ?activecode
      ,email_id = ?emailid
      ,modfctn_date =?modificationdate
      ,modfctn_by = ?modificationby
      ,modfctn_termnl = ?modificationterminal
       ,password_expired=?passwordexpired
        ,m_travelAgentId=?m_travelAgentId
        ,m_userType=?m_userType
,m_macAddress=?m_macAddress
,m_printerNameBP=?m_printerNameBP
,m_printerNameWC=?m_printerNameWC
,m_directPrintBP=?m_directPrintBP
,m_directPrintWC=?m_directPrintWC
,m_printerNameKIMS=?m_printerNameKIMS
,m_directPrintKIMS=?m_directPrintKIMS
 WHERE user_id = ?userid";
            return sql;
        }

        public static string UpdateUserPrinterOptions()
        {
            string sql = @"UPDATE user_master
   SET 
m_macAddress=?m_macAddress
,m_printerNameBP=?m_printerNameBP
,m_printerNameWC=?m_printerNameWC
,m_directPrintBP=?m_directPrintBP
,m_directPrintWC=?m_directPrintWC
,m_printerNameKIMS=?m_printerNameKIMS
,m_directPrintKIMS=?m_directPrintKIMS
 WHERE user_id = ?userid";
            return sql;
        }
        public static string UpdatePasswordUser()
        {
            string sql = @"UPDATE user_master
   SET password = ?password     
      ,modfctn_date =?modificationdate
      ,modfctn_by = ?modificationby
      ,modfctn_termnl = ?modificationterminal
      ,expiry_date=?expirydate
 WHERE user_id = ?userid";
            return sql;
        }
        public static string DeleteUser()
        {
            string sql = @"DELETE FROM user_master
 WHERE user_id = ?userid";
            return sql;
        }
        public static string InsertLogUser()
        {
            return @"INSERT INTO user_log_table
           (user_id
           ,log_in_date
           ,session_code
           ,remark)
     VALUES
           (?userid, ?logindate, ?sessioncode, ?remark)";
        }
        public static string UpdateLogoutUser()
        {
            return @"UPDATE user_log_table
   SET log_out_date = ?logoutdate     
 WHERE session_code = ?sessioncode";
        }
        public static string CountUserLog()
        {
            return @"SELECT COUNT(*)
  FROM user_log_table where session_code = ?sessioncode";
        }
        public static string InsertRoleMaster()
        {
            return @"INSERT INTO role_master
           (role_master_name
           ,cretn_date
           ,cretn_by
           ,modfctn_date
           ,modfctn_by)
     VALUES
           (?rolemastername
           ,?cretndate
           ,?cretnby
           ,?modfctndate
           ,?modfctnby)";
        }
        public static string GetMaxRoleMasterID()
        {
            return @"SELECT MAX(role_master_id) FROM role_master";
        }
        public static string GetRoleMasterByID()
        {
            return @"SELECT * FROM role_master where role_master_id = ?rolemasterid";
        }
        public static string GetAllRoleMaster()
        {
            return @"SELECT * FROM role_master";
        }
        public static string UpdateRoleMaster()
        {
            return @"UPDATE role_master
   SET role_master_name = ?rolemastername
      ,modfctn_date = ?modfctndate
      ,modfctn_by = ?modfctnby
 WHERE role_master_id = ?rolemasterid";
        }
        public static string DeleteRoleMaster()
        {
            return @"DELETE FROM role_master
      WHERE  role_master_id = ?rolemasterid";
        }
        public static string InsertRoleDetil()
        {
            return @"INSERT INTO role_detail
           (role_master_id
           ,view_flag
           ,new_flag
           ,update_flag
            , form_name
            ,delete_flag)
     VALUES
           (?rolemasterid
           ,?viewflag
           ,?newflag
           ,?updateflag
            ,?formname
            ,?deleteflag
            )";
        }
        public static string DeleteRoleDetilByRoleMaster()
        {
            return @"DELETE FROM role_detail
      WHERE role_master_id = ?rolemasterid";
        }

        public static string SearchUserByCodeName(string p)
        {
            string sql = "select * from user_master WHERE concat(user_code,user_name) like '%"+p+"%'";
            return sql;
        }
        public static string SearchUserByCodeNameAndTravelAgent(string p)
        {
            string sql = "select * from user_master WHERE m_travelAgentId=?m_travelAgentId and concat(user_code,user_name) like '%" + p + "%'";
            return sql;
        }
        public static string SearchRoleMasterByCodeName()
        {
            string sql = "select * from role_master WHERE role_master_name like '%'+?name+'%'";
            return sql;
        }
        public static string AddUserHistory()
        {
            string sql = @"INSERT INTO user_history
           (
            user_id
           ,start_date
            ,end_date
            ,session_code
            ,remarks
            )
     VALUES
           (?user_id
           ,?start_date
            ,?end_date
            ,?session_code
            ,?remarks)"; 
            return sql;
        }
        public static string GetMaxUserHistoryID()
        {
            return "select max(id) from user_history";
        }
        public static string UpdateUserHistory()
        {
            return @"UPDATE user_history set start_date = ?start_date
            ,end_date = ?end_date
            ,session_code =?session_code
            ,remarks = ?remarks where id = ?id";
        }
        public static string UpdateEndDateUserHistory()
        {
            return @"UPDATE user_history set end_date = ?end_date where id = ?id";
        }

        public static string InsertUserRole()
        {
            return @"INSERT INTO user_role
           (user_id
           ,role_master_id)
     VALUES
           (?userid
           ,?rolemasterid)";
        }
        public static string DeleteUserRoleByUserID()
        {
            return @"DELETE FROM user_role
      WHERE user_id = ?userid";
        }
        public static string GetUserRoleByUserID()
        {
            return @"SELECT * FROM user_role
      WHERE user_id = ?userid";
        }


        public static string GetRoleDetilByRoleMaster()
        {
            return @"SELECT * FROM role_detail
      WHERE role_master_id = ?rolemasterid";
        }
    }
}
